package com.feidee.data.report.dao.impl;

import com.feidee.data.report.constant.Constant;
import com.feidee.data.report.dao.ICRCommonOprDao;
import com.feidee.data.report.model.ColumnInfoModel;
import com.feidee.data.report.model.CustomReportModel;
import com.feidee.data.report.model.ReportConfiguration;
import com.feidee.data.report.model.SqlInfoModel;
import com.feidee.data.report.util.DbOperationUtil;
import com.feidee.data.report.util.ResultResponseUtil;
import com.feidee.data.report.util.ToolUtils;
import com.feidee.fdcommon.configuration.CustomConfiguration;
import org.apache.log4j.Logger;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * 自定义报表提交接口和发布接口公用方法
 */
public class CRCommonOprDaoImpl implements ICRCommonOprDao {

    private static Logger logger = Logger.getLogger(CRCommonOprDaoImpl.class);

    public DataSource confDataSource;
    public DataSource cusMysqlDataSource;

    public CRCommonOprDaoImpl() {
    }

    /**
     * 创建hive sql对应的mysql,并写入配置库
     *
     * @param crm
     * @return int
     */
    public int writeMysqlStrToDB(final CustomReportModel crm, String pubStatus) {

        Connection mysqlConnCre = null;
        PreparedStatement statement = null;
        Connection mysqlConnConf = null;
        PreparedStatement selectStatement = null;
        PreparedStatement deleteStatement = null;
        PreparedStatement createStatement = null;

        try {
            mysqlConnConf = confDataSource.getConnection();
            // 存储切换到spark执行引擎的SQL下标
            HashSet<String> isSpark = new HashSet<>();
            String selectSql = "SELECT `report_index` FROM report_sql WHERE report_key = '" + crm.getApplysqlname() + "' AND `engine`='spark'";
            logger.info(selectSql);
            selectStatement = mysqlConnConf.prepareStatement(selectSql);
            ResultSet selectResultSet = selectStatement.executeQuery();
            while (selectResultSet.next()) {
                String sparkIndex = selectResultSet.getString("report_index");
                isSpark.add(sparkIndex);
                logger.info("报表 " + crm.getApplysqlname() + " 第: " + sparkIndex + " 条SQL已切换到spark执行引擎!");
            }
            DbOperationUtil.cleanConnEnv(selectResultSet);

            // 报表优先级
            String priority = crm.getPriority();
            // 数据源
            String dataResources = crm.getDataResources();
            //删除配置库中原配置
            String deleteSql = "DELETE FROM report_sql WHERE report_key = '" + crm.getApplysqlname() + "'";
            deleteStatement = mysqlConnConf.prepareStatement(deleteSql);
            deleteStatement.execute();
            logger.info("自定义报表 " + crm.getId() + " => " + crm.getApplysqlname()
                    + " 删除原配置成功！Run SQL: " + deleteSql);

            //拼接Mysql
            Map<String, ReportConfiguration> dataMap = createMysqlStr(crm, pubStatus);
            if (0 == dataMap.size()) {
                return -1;
            }

            //将拼接的sql写入配置库
            String mysqlInsStr = "insert into report_sql (report_key,report_index,hqlcontext,sqlcontext,priority,engine," +
                    "hqlhistory,run_date,is_enable,report_dependency,data_resources,is_schedule,is_custom) " +
                    "VALUES (?,?,?,?,?,?,?,?,?,?,?,1,1) on duplicate key update " +
                    "`report_key`=?,`report_index`=?,`hqlcontext`=?,`sqlcontext`=?,`priority`=?,`engine`=?" +
                    ",`hqlhistory`=?,`run_date`=?,`is_enable`=?,`report_dependency`=?,`data_resources`=?,`is_schedule`=1" +
                    ",`is_custom`=1";
            createStatement = mysqlConnConf.prepareStatement(mysqlInsStr);
            mysqlConnConf.setAutoCommit(false);
            Set<Map.Entry<String, ReportConfiguration>> entries = dataMap.entrySet();
            for (Map.Entry<String, ReportConfiguration> entry : entries) {
                ReportConfiguration rconf = entry.getValue();
                // 记录执行引擎
                String engine = rconf.getEngine();
                String reportIndex = rconf.getReport_index();
                if (isSpark.contains(reportIndex) && "hive".equals(engine)) {
                    engine = "spark";
                }
                createStatement.setString(1, rconf.getReport_key());
                createStatement.setString(2, reportIndex);
                createStatement.setString(3, rconf.getHqlcontext());
                createStatement.setString(4, rconf.getSqlcontext());
                createStatement.setString(5, priority);
                createStatement.setString(6, engine);
                createStatement.setString(7, rconf.getSqlHistory());
                createStatement.setString(8, rconf.getRun_date());
                createStatement.setString(9, rconf.getIsEnable());
                createStatement.setString(10, rconf.getReportDependency());
                createStatement.setString(11, dataResources);
                createStatement.setString(12, rconf.getReport_key());
                createStatement.setString(13, reportIndex);
                createStatement.setString(14, rconf.getHqlcontext());
                createStatement.setString(15, rconf.getSqlcontext());
                createStatement.setString(16, priority);
                createStatement.setString(17, engine);
                createStatement.setString(18, rconf.getSqlHistory());
                createStatement.setString(19, rconf.getRun_date());
                createStatement.setString(20, rconf.getIsEnable());
                createStatement.setString(21, rconf.getReportDependency());
                createStatement.setString(22, dataResources);
                createStatement.addBatch();
            }
            createStatement.executeBatch();
            mysqlConnConf.commit();
            logger.info("自定义报表 " + crm.getId() + " => " + crm.getApplysqlname() + " 拼接MySql语句写入配置库成功");
            return 0;
        } catch (SQLException e) {
            logger.info("自定义报表 " + crm.getId() + " => " + crm.getApplysqlname() + " 拼接MySql语句写入配置库失败,", e);
            ResultResponseUtil.resultResponse(CustomConfiguration.getString("returnurl", Constant.TASK_RES_RETURN_URI), crm.getId(),
                    "3", "拼接MySql语句写入配置库失败," + e.getMessage());
            return -1;
        } finally {
            DbOperationUtil.cleanConnEnv(mysqlConnCre, statement);
            DbOperationUtil.cleanConnEnv(mysqlConnConf, deleteStatement);
            DbOperationUtil.cleanConnEnv(createStatement);
            DbOperationUtil.cleanConnEnv(selectStatement);
        }
    }

    /**
     * 创建写入配置库的Mysql语句
     *
     * @param crm
     * @param pubStatus
     * @return
     */
    public Map<String, ReportConfiguration> createMysqlStr(CustomReportModel crm, String pubStatus) {

        Map<String, ReportConfiguration> dataMap = new HashMap<String, ReportConfiguration>();
        Map<String, SqlInfoModel> hivesql = crm.getHivesql();
        Map<String, ColumnInfoModel> mysqlColumn = crm.getMysqlColumn();
        Pattern tagPattern = Pattern.compile(".*?as (.*?)[,| ].*?");
        for (String key : hivesql.keySet()) {
            SqlInfoModel sqlInfoModel = hivesql.get(key);
            String sqlContext = null;
            String field;
            // 2019-12-16号 为修改replace into为insert into新增末尾字符串
            String endStr = "";
            // 提取每个sql第一个from之前的语句,并正则取出别名
            String hiveSqlStr = sqlInfoModel.getSqlContent();
            String writeHiveSqlStr = hiveSqlStr;
            String sqlHistory = sqlInfoModel.getSqlHistory();
            String writesqlHistory = sqlHistory;
            if (sqlInfoModel.getSqlSource().equals("mysql")) {
                writeHiveSqlStr = writeHiveSqlStr.replace(Constant.CUSTOM_REPORT_MYSQL_TABLE_SELF,
                        "`" + Constant.CUSTOM_REPORT_MYSQL_TABLE_PREFIX + crm.getApplysqlname() + "`");
                writesqlHistory = writesqlHistory.replace(Constant.CUSTOM_REPORT_MYSQL_TABLE_SELF, "`" + crm.getApplysqlname() + "`");
                hiveSqlStr = hiveSqlStr.replace(Constant.CUSTOM_REPORT_MYSQL_TABLE_SELF,
                        "`" + Constant.CUSTOM_REPORT_MYSQL_TABLE_PREFIX + crm.getApplysqlname() + "`");
                sqlHistory = sqlHistory.replace(Constant.CUSTOM_REPORT_MYSQL_TABLE_SELF, "`" + crm.getApplysqlname() + "`");
            }

            hiveSqlStr = hiveSqlStr.replaceAll("\r\n", " ").replaceAll("\r", " ").replaceAll("\n", " ").replaceAll(" +", " ");
            hiveSqlStr = hiveSqlStr.replace(";", "").replace("\t", " ").toLowerCase();
            sqlHistory = sqlHistory.replaceAll("\r\n", " ").replaceAll("\r", " ").replaceAll("\n", " ").replaceAll(" +", " ");
            sqlHistory = sqlHistory.replace(";", "").replace("\t", " ").toLowerCase();

            int indexOfQuota = hiveSqlStr.indexOf(" from(") + 1;
            int indexOfSpace = hiveSqlStr.indexOf(" from ") + 1;
            if (indexOfQuota == 0 && indexOfSpace == 0) {
                logger.info("自定义报表 " + crm.getId() + " => " + crm.getApplysqlname()
                        + " HiveSql获取别名失败, HiveSql: " + hiveSqlStr);
                ResultResponseUtil.resultResponse(CustomConfiguration.getString("returnurl", Constant.TASK_RES_RETURN_URI),
                        crm.getId(), "3", "HiveSql语句错误," + "HiveSql获取别名失败");
                return dataMap;
            }

            int indexOf = -1;
            if (0 == indexOfSpace) {
                indexOf = indexOfQuota;
            } else if (0 == indexOfQuota) {
                indexOf = indexOfSpace;
            } else if (indexOfSpace > indexOfQuota) {
                indexOf = indexOfQuota;
            } else {
                indexOf = indexOfSpace;
            }
            String hiveSqlTem = ToolUtils.replaceWrongAs(hiveSqlStr.substring(0, indexOf).replaceAll(" +", " "));
            Matcher tagMatcher = tagPattern.matcher(hiveSqlTem);
            String oprtype = sqlInfoModel.getSqlType();
            String engine = sqlInfoModel.getSqlSource();
            // 在对各条hivesql拼接对应Mysql语句时,判断每条hivesql解析出来的别名是否在上传的Mysql列信息中存在
            // 如果存在则进行拼接,如果不存在则直接结束进行下个别名处理
            if (oprtype.equals("insert")) {
                int count = 0;
                sqlContext = "insert into `" + Constant.CUSTOM_REPORT_MYSQL_TABLE_PREFIX + crm.getApplysqlname() + "` (";
                while (tagMatcher.find()) {
                    field = tagMatcher.group(1).trim();
                    if (!mysqlColumn.containsKey(field))
                        continue;
                    sqlContext += "`" + field + "`" + ",";
                    endStr += "`" + field + "`" + "=?,";
                    count++;
                }
                logger.info("末尾字符串：" + endStr + "字符串长度为:" + endStr.length());

                sqlContext = sqlContext.substring(0, sqlContext.length() - 1) + ") VALUES(";
                for (int i = 0; i < count; i++) {
                    if (i == count - 1) {
                        sqlContext += "?)";
                    } else {
                        sqlContext += "?,";
                    }
                }
                sqlContext += " " + "on duplicate key update " + endStr.substring(0, endStr.length() - 1);
            }
            if (oprtype.equals("update")) {
                String sqlContextSet = " SET ";
                String sqlContextWhe = " WHERE ";
                sqlContext = "UPDATE `" + Constant.CUSTOM_REPORT_MYSQL_TABLE_PREFIX + crm.getApplysqlname() + "`";
                while (tagMatcher.find()) {
                    field = tagMatcher.group(1);
                    if (!mysqlColumn.containsKey(field))
                        continue;
                    if (mysqlColumn.get(field).getDimType().equals("1")) {
                        if (sqlContextWhe.equals(" WHERE ")) {
                            sqlContextWhe += "`" + tagMatcher.group(1) + "`" + "=?";
                        } else {
                            sqlContextWhe += " AND " + "`" + tagMatcher.group(1) + "`" + "=?";
                        }
                    } else {
                        sqlContextSet += "`" + tagMatcher.group(1) + "`" + "=?,";
                    }
                }
                sqlContext += sqlContextSet.substring(0, sqlContextSet.length() - 1);
                // 如果update不存在主键,即不存在where子句,本不需拼接where子句,但是单纯的update语句运行会导致大量全表更新，
                // 所以仍然拼接上where条件，让其报错，避免后期mysql压力过大
//				if (!sqlContextWhe.equals(" WHERE ")) {
                sqlContext += sqlContextWhe;
//				}
            }
            logger.info("自定义报表 " + crm.getId() + " => " + crm.getApplysqlname() + ", Index: "
                    + key.substring(3) + ",HiveSql: " + hiveSqlStr + " ,拼接的Mysql: " + sqlContext);

            //去除无效的SQL，可能包含一个sql除了维度，其他指标都没有选择，导致拼接出如下形式的sql：
            //UPDATE `tmp_report_test25` SET WHERE `ymd`=? ，这种形式的sql需要去掉
            if (null != sqlContext && sqlContext.contains("SET WHERE")) {
                continue;
            }

            if (!ToolUtils.isNotNull(sqlHistory)) {
                sqlHistory = "";
            }
            if (!ToolUtils.isNotNull(pubStatus)) {
                pubStatus = Constant.CR_PUBSTATUS_NO_PUBLISH;
            }
            String period = crm.getPeriod();
            if (!ToolUtils.isNotNull(period)) {
                period = "7";
            }

            ReportConfiguration confDataModel = new ReportConfiguration();
            confDataModel.setReport_key(crm.getApplysqlname());
            confDataModel.setReport_index(key.substring(3));
            confDataModel.setHqlcontext(writeHiveSqlStr);
            confDataModel.setSqlcontext(sqlContext);
            confDataModel.setEngine(engine);
            confDataModel.setSqlHistory(writesqlHistory);
            confDataModel.setRun_date(period);
            confDataModel.setIsEnable(pubStatus);
            confDataModel.setOperation("select");
            confDataModel.setProcess_class("");
            confDataModel.setTable_type("horizontal");
            confDataModel.setMysql_table("");
            confDataModel.setMysql_keys("");
            confDataModel.setIsCustom("1");
            confDataModel.setEnqueueTime(System.currentTimeMillis());
            confDataModel.setReportDependency(crm.getReportDependency());
            confDataModel.setRunHistory("0");
            confDataModel.setRunPriority(1);
            //修复自定义报表优先级bug
            confDataModel.setTablePriority(Integer.parseInt(crm.getPriority()));

            dataMap.put(key.substring(3), confDataModel);
        }

        return dataMap;
    }

    /**
     * 修改报表执行周期
     *
     * @param crm
     * @param pubStatus
     * @return
     */
    public int modifyReportPeriod(CustomReportModel crm, String pubStatus) {
        Connection mysqlChangeConnConf = null;
        PreparedStatement mysqlChangeStaConf = null;
        String updatePeriConfStr = null;
        try {
            mysqlChangeConnConf = confDataSource.getConnection();
            updatePeriConfStr = "UPDATE report_sql SET `run_date`='" + crm.getPeriod()
                    + "' WHERE `report_key`='" + crm.getApplysqlname() + "'";
            mysqlChangeStaConf = mysqlChangeConnConf.prepareStatement(updatePeriConfStr);
            mysqlChangeStaConf.execute();
            logger.info("自定义报表 " + crm.getId() + " => " + crm.getApplysqlname()
                    + " 更新报表更新周期成功，RUNSQL: " + updatePeriConfStr);
        } catch (SQLException e) {
            logger.info("自定义报表 " + crm.getId() + " => " + crm.getApplysqlname()
                    + " 更新报表更新周期失败，RUNSQL: " + updatePeriConfStr, e);
            return -1;
        } finally {
            DbOperationUtil.cleanConnEnv(mysqlChangeConnConf, mysqlChangeStaConf);
        }
        return 0;
    }

    /**
     * 修改报表优先级
     *
     * @param crm
     * @param pubStatus
     * @return
     */
    public int modifyReportPriority(CustomReportModel crm, String pubStatus) {
        Connection mysqlChangeConnConf = null;
        PreparedStatement mysqlChangeStaConf = null;
        String updatePeriConfStr = null;
        try {
            mysqlChangeConnConf = confDataSource.getConnection();
            updatePeriConfStr = "UPDATE report_sql SET `priority`='" + crm.getPriority()
                    + "' WHERE `report_key`='" + crm.getApplysqlname() + "'";
            mysqlChangeStaConf = mysqlChangeConnConf.prepareStatement(updatePeriConfStr);
            mysqlChangeStaConf.execute();
            logger.info("自定义报表 " + crm.getId() + " => " + crm.getApplysqlname()
                    + " 更新报表优先级成功，RUNSQL: " + updatePeriConfStr);
            // 如果已发布则将该报表依赖的报表优先级提高到该报表相同的优先级
            if (Constant.CR_PUBSTATUS_ALREADY_PUBLISH.equals(pubStatus)) {
                updateDependencyReportPriority(crm.getApplysqlname(), Integer.valueOf(crm.getPriority()));
            }
        } catch (SQLException e) {
            logger.info("自定义报表 " + crm.getId() + " => " + crm.getApplysqlname()
                    + " 更新报表优先级失败，RUNSQL: " + updatePeriConfStr, e);
            return -1;
        } finally {
            DbOperationUtil.cleanConnEnv(mysqlChangeConnConf, mysqlChangeStaConf);
        }
        return 0;
    }

    /**
     * 修改数据源
     *
     * @param crm
     * @param pubStatus
     * @return
     */
    public int modifyDataResources(CustomReportModel crm, String pubStatus) {
        Connection mysqlChangeConnConf = null;
        PreparedStatement mysqlChangeStaConf = null;
        String updatePeriConfStr = null;
        try {
            mysqlChangeConnConf = confDataSource.getConnection();
            updatePeriConfStr = "UPDATE report_sql SET `data_resources`='" + crm.getDataResources()
                    + "' WHERE `report_key`='" + crm.getApplysqlname() + "'";
            mysqlChangeStaConf = mysqlChangeConnConf.prepareStatement(updatePeriConfStr);
            mysqlChangeStaConf.execute();
            logger.info("自定义报表 " + crm.getId() + " => " + crm.getApplysqlname()
                    + " 更新数据源成功，RUNSQL: " + updatePeriConfStr);
        } catch (SQLException e) {
            logger.info("自定义报表 " + crm.getId() + " => " + crm.getApplysqlname()
                    + " 更新数据源失败，RUNSQL: " + updatePeriConfStr, e);
            return -1;
        } finally {
            DbOperationUtil.cleanConnEnv(mysqlChangeConnConf, mysqlChangeStaConf);
        }
        return 0;
    }

    /**
     * 更改依赖报表优先级（提高优先级到当前报表的优先级，若比当前优先级更高不会修改）
     *
     * @param reportName
     * @param priority
     */
    private void updateDependencyReportPriority(String reportName, int priority) {
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
        ResultSet previewResultSet = null;
        try {
            connection = confDataSource.getConnection();
            String selectSql = "SELECT DISTINCT report_dependency FROM report_sql WHERE report_key='" + reportName + "'";
            statement = connection.createStatement();
            resultSet = statement.executeQuery(selectSql);
            if (resultSet.next()) {
                String updateKey = "";
                String reportDependency = resultSet.getString("report_dependency");
                String previewPrioritySql = "SELECT report_key,priority FROM report_sql WHERE report_key IN ('" +
                        reportDependency.replace(",", "','") + "')";
                previewResultSet = statement.executeQuery(previewPrioritySql);
                while (previewResultSet.next()) {
                    int dependentPriority = previewResultSet.getInt("priority");
                    if (dependentPriority < priority) {
                        String reportKey = previewResultSet.getString("report_key");
                        updateKey += "'" + reportKey + "',";
                    }
                }
                if (!updateKey.equals("")) {
                    String updateSql = "UPDATE report_sql SET priority=" + priority + " WHERE report_key IN (" +
                            updateKey.substring(0, updateKey.length() - 1) + ")";
                    statement.execute(updateSql);
                    logger.info("自定义报表 " + reportName
                            + " 更新依赖报表优先级成功，RUNSQL: " + updateSql);
                }
            }
        } catch (SQLException e) {
            logger.info("自定义报表 " + reportName
                    + " 更新依赖报表优先级失败", e);
        } finally {
            DbOperationUtil.cleanConnEnv(connection, statement, resultSet);
            DbOperationUtil.cleanConnEnv(previewResultSet);
        }
    }


    @Override
    public Map<String, String> updateReportConfig(List<String> sqlList) {
        Map<String, String> map = new HashMap<>();
        Connection mysqlChangeConnConf = null;
        PreparedStatement mysqlChangeStaConf = null;
        String updatePeriConfStr = null;
        try {
            mysqlChangeConnConf = confDataSource.getConnection();
            for (String sql : sqlList) {
                try {
                    updatePeriConfStr = sql;
                    mysqlChangeStaConf = mysqlChangeConnConf.prepareStatement(updatePeriConfStr);
                    mysqlChangeStaConf.execute();
                    logger.info("报表配置更新成功 " + "=>" + " 执行SQL: " + updatePeriConfStr);
                } catch (Exception e) {
                    map.put(updatePeriConfStr, e.getMessage());
                }
            }
        } catch (SQLException e) {
            logger.info("报表配置更新失败 " + "=>" + " 执行SQL: " + updatePeriConfStr);
            map.put(updatePeriConfStr, e.getMessage());
        } finally {
            DbOperationUtil.cleanConnEnv(mysqlChangeConnConf, mysqlChangeStaConf);
        }
        return map;
    }

    /**
     * 修改Mysql列注释
     *
     * @param crm
     * @param pubStatus
     * @return
     */
    public int modifyMysqlColNote(CustomReportModel crm, String pubStatus) {

        Connection mysqlChangeConn = null;
        PreparedStatement mysqlChangeSta = null;
        try {
            String mysqlStrChange;
            String mysqlStrChangeTemp;
            mysqlChangeConn = DbOperationUtil.getMysqlConntion(Constant.CUSTOM_REPORT_FLAG);
            Map<String, ColumnInfoModel> mysqlColumn = crm.getMysqlColumn();
            if (pubStatus.equals(Constant.CR_PUBSTATUS_NO_PUBLISH)) {
                mysqlStrChangeTemp = "ALTER TABLE `" + Constant.CUSTOM_REPORT_MYSQL_TABLE_PREFIX
                        + crm.getApplysqlname() + "` MODIFY COLUMN ";
            } else {
                mysqlStrChangeTemp = "ALTER TABLE `" + crm.getApplysqlname() + "` MODIFY COLUMN ";
            }

            for (String key : mysqlColumn.keySet()) {
                ColumnInfoModel columnInfoModel = mysqlColumn.get(key);
                String columnType = columnInfoModel.getColumnType();
                if (columnType.toLowerCase().contains("varchar")) {
                    mysqlStrChange = mysqlStrChangeTemp + "`" + key + "`" + " " + columnInfoModel.getColumnType()
                            + " DEFAULT '' COMMENT '" + columnInfoModel.getColumnValue() + "'";
                } else {
                    mysqlStrChange = mysqlStrChangeTemp + "`" + key + "`" + " " + columnInfoModel.getColumnType()
                            + " DEFAULT '0' COMMENT '" + columnInfoModel.getColumnValue() + "'";
                }
                mysqlChangeSta = mysqlChangeConn.prepareStatement(mysqlStrChange);
                mysqlChangeSta.execute();
            }
            logger.info("自定义报表 " + crm.getId() + " => " + crm.getApplysqlname() + " 更新报表字段注释成功!");
        } catch (Exception e) {
            logger.info("自定义报表 " + crm.getId() + " => " + crm.getApplysqlname() + " 更新报表字段注释失败!");
            DbOperationUtil.cleanConnEnv(mysqlChangeConn, mysqlChangeSta);
            return -1;
        } finally {
            DbOperationUtil.cleanConnEnv(mysqlChangeConn, mysqlChangeSta);
        }
        return 0;
    }

    /**
     * 修改Mysql表注释
     *
     * @param crm
     * @param pubStatus
     * @return
     */
    public int modifyMysqlTblNote(CustomReportModel crm, String pubStatus) {
        Connection mysqlChangeConn = null;
        PreparedStatement mysqlChangeSta = null;
        String mysqlStrChange = null;
        try {
            if (Constant.CR_PUBSTATUS_ALREADY_PUBLISH.equals(pubStatus)) {
                mysqlStrChange = "ALTER TABLE `" + crm.getApplysqlname() + "` COMMENT '" + crm.getApplyname() + "'";
            } else {
                mysqlStrChange = "ALTER TABLE `" + Constant.CUSTOM_REPORT_MYSQL_TABLE_PREFIX + crm.getApplysqlname()
                        + "` COMMENT '" + crm.getApplyname() + "'";
            }
            mysqlChangeConn = DbOperationUtil.getMysqlConntion(Constant.CUSTOM_REPORT_FLAG);
            mysqlChangeSta = mysqlChangeConn.prepareStatement(mysqlStrChange);
            mysqlChangeSta.execute();
            logger.info("自定义报表 " + crm.getId() + " => " + crm.getApplysqlname()
                    + " 更新报表注释成功，RUNSQL: " + mysqlStrChange);
        } catch (Exception e) {
            logger.info("自定义报表 " + crm.getId() + " => " + crm.getApplysqlname()
                    + " 更新报表注释失败，RUNSQL: " + mysqlStrChange, e);
            return -1;
        } finally {
            DbOperationUtil.cleanConnEnv(mysqlChangeConn, mysqlChangeSta);
        }
        return 0;
    }

    /**
     * 修改历史sql的值
     *
     * @param crm
     * @param pubStatus
     * @return
     */
    public int modifyHistorySqlNote(CustomReportModel crm, String pubStatus) {
        Connection mysqlConnUpdate = null;
        PreparedStatement statementUpdate = null;
        Map<String, SqlInfoModel> newHiveSql = crm.getHivesql();
        try {
            mysqlConnUpdate = confDataSource.getConnection();
            mysqlConnUpdate.setAutoCommit(false);
            String keyTem = "sql";
            String key;

            String modifyHistoryStr = "UPDATE report_sql SET hqlhistory=? WHERE report_key='" + crm.getApplysqlname()
                    + "' AND report_index=?";
            statementUpdate = mysqlConnUpdate.prepareStatement(modifyHistoryStr);
            for (int i = 1; i <= newHiveSql.size(); i++) {
                key = keyTem + i;
                //如果是Mysql类型，需要替换掉sql内部的temp_table字段
                String sqlHistory = newHiveSql.get(key).getSqlHistory();
                if (newHiveSql.get(key).getSqlSource().equals("mysql")) {
                    sqlHistory = sqlHistory.replace(Constant.CUSTOM_REPORT_MYSQL_TABLE_SELF, "`" + crm.getApplysqlname() + "`");
                }
                statementUpdate.setString(1, sqlHistory);
                statementUpdate.setString(2, String.valueOf(i));
                statementUpdate.addBatch();
            }
            statementUpdate.executeBatch();
            mysqlConnUpdate.commit();
            logger.info("自定义报表 " + crm.getId() + " => " + crm.getApplysqlname() + " 更改历史SQL成功!");
            return 0;
        } catch (SQLException e) {
            logger.info("自定义报表 " + crm.getId() + " => " + crm.getApplysqlname() + " 更改历史SQL失败!", e);
            return -1;
        } finally {
            DbOperationUtil.cleanConnEnv(mysqlConnUpdate, statementUpdate);
        }
    }

    /**
     * 修改报表依赖信息字段
     *
     * @param crm
     * @param pubStatus
     * @return
     */
    public int modifyReportDependency(CustomReportModel crm, String pubStatus) {
        Connection mysqlChangeConnConf = null;
        PreparedStatement mysqlChangeStmt = null;
        String updateReportDependencyStr = null;
        try {
            mysqlChangeConnConf = confDataSource.getConnection();
            updateReportDependencyStr = "UPDATE report_sql SET `report_dependency`='" + crm.getReportDependency()
                    + "' WHERE `report_key`='" + crm.getApplysqlname() + "'";
            mysqlChangeStmt = mysqlChangeConnConf.prepareStatement(updateReportDependencyStr);
            mysqlChangeStmt.execute();
            logger.info("自定义报表 " + crm.getId() + " => " + crm.getApplysqlname()
                    + " 更新报表依赖信息成功，RUNSQL: " + updateReportDependencyStr);
        } catch (SQLException e) {
            logger.info("自定义报表 " + crm.getId() + " => " + crm.getApplysqlname()
                    + " 更新报表依赖信息失败，RUNSQL: " + updateReportDependencyStr, e);
            return -1;
        } finally {
            DbOperationUtil.cleanConnEnv(mysqlChangeConnConf, mysqlChangeStmt);
        }
        return 0;
    }

    public DataSource getConfDataSource() {
        return confDataSource;
    }

    public void setConfDataSource(DataSource confDataSource) {
        this.confDataSource = confDataSource;
    }

    public DataSource getCusMysqlDataSource() {
        return cusMysqlDataSource;
    }

    public void setCusMysqlDataSource(DataSource cusMysqlDataSource) {
        this.cusMysqlDataSource = cusMysqlDataSource;
    }

}
